﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NewBee.Core.Data
{
    #region SQL类型
    /// <summary>
    /// SQL类型
    /// </summary>
    public enum SQLType
    {
        /// <summary>
        /// 新增
        /// </summary>
        INSERT = 0,
        /// <summary>
        /// 修改
        /// </summary>
        UPDATE,
        /// <summary>
        /// 删除
        /// </summary>
        DELETE,
        /// <summary>
        /// 查询
        /// </summary>
        SELECT,
        /// <summary>
        /// 分页查询
        /// </summary>
        PAGE_SELECT,
    }
    #endregion

    #region 分页帮助类
    /// <summary>
    /// 分页帮助类
    /// </summary>
    public class PageHelper
    {
        /// <summary>
        /// 每页行数
        /// </summary>
        public int rows { get; set; }
        /// <summary>
        /// 当前页码
        /// </summary>
        public int page { get; set; }

        public PageHelper()
        {
            rows = 20;
            page = 0;
        }
    }
    #endregion

    #region SQL帮助类
    /// <summary>
    /// SQL帮助类
    /// </summary>
    public class SQLHelper
    {
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get; private set; }
        /// <summary>
        /// 是否去重
        /// </summary>
        public bool IsDistinct { get; private set; }
        /// <summary>
        /// 查询字段列表，逗号分隔
        /// </summary>
        public IList<string> SelectColumns { get; private set; }
        /// <summary>
        /// where条件，仅满足等于的查询方式的and方式
        /// </summary>
        public IList<string> WhereParamters { get; private set; }
        /// <summary>
        /// Where查询语句，用于复杂查询
        /// </summary>
        public string WhereSQL { get; private set; }
        /// <summary>
        /// 排序条件，KeyValuePair(查询字段索引,t/f：正序/反序)
        /// </summary>
        public IDictionary<string, bool> OrderParamaters { get; private set; }
        /// <summary>
        /// 分组条件，查询字段索引
        /// </summary>
        public IList<string> GroupByParamaters { get; private set; }
        /// <summary>
        /// 新增/更新参数列表
        /// </summary>
        public IList<string> EditParamaters { get; private set; }
        /// <summary>
        /// top数
        /// </summary>
        public int TopCount { get; private set; }
        /// <summary>
        /// SQL类型
        /// </summary>
        public SQLType SQLType { get; private set; }
        /// <summary>
        /// 分页帮助
        /// </summary>
        public PageHelper PageHelper { get; private set; }

        /// <summary>
        /// 新增语句
        /// </summary>
        private static string INSERT_SQL = @"INSERT INTO {0} ({1}) VALUES ({2})";
        /// <summary>
        /// 修改语句
        /// </summary>
        private static string UPDATE_SQL = @"UPDATE {0} SET {1} WHERE {2}";
        /// <summary>
        /// 删除语句
        /// </summary>
        private static string DELETE_SQL = @"DELETE FROM {0} WHERE {1}";
        /// <summary>
        /// 查询语句
        /// </summary>
        private static string SELECT_SQL = @"SELECT {1} FROM {0} WHERE {2}";
        /// <summary>
        /// 分页查询语句
        /// </summary>
        private static string SELECT_PAGE_SQL = "SELECT {0} * FROM ( SELECT ROW_NUMBER() OVER ({1}) AS ROW , {2} FROM {3} WHERE {4} ) AS DATALIST WHERE ROW > {5}";

        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLHelper() : this(string.Empty)
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        public SQLHelper(string tableName)
        {
            TopCount = 0;
            SelectColumns = new List<string>();
            SQLType = SQLType.SELECT;
            GroupByParamaters = new List<string>();
            WhereParamters = new List<string>();
            OrderParamaters = new Dictionary<string, bool>();
            TableName = tableName;
            PageHelper = new PageHelper();
        }

        /// <summary>
        /// 设置分页
        /// </summary>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public SQLHelper Page(int page, int rows)
        {
            SQLType = SQLType.PAGE_SELECT;
            PageHelper = new PageHelper { page = page, rows = rows };
            return this;
        }

        /// <summary>
        /// 设置去重
        /// </summary>
        /// <param name="isDistinct">是否去重</param>
        /// <returns></returns>
        public SQLHelper Distinct(bool isDistinct)
        {
            IsDistinct = isDistinct;
            return this;
        }

        /// <summary>
        /// 设置查询字段
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public SQLHelper Columns(IList<string> columns)
        {
            SelectColumns = columns;
            return this;
        }

        /// <summary>
        /// 设置置顶数量
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        public SQLHelper Top(int count)
        {
            TopCount = count;
            return this;
        }

        /// <summary>
        /// 设置where语句
        /// </summary>
        /// <param name="whereString"></param>
        /// <returns></returns>
        public SQLHelper Where(string whereString)
        {
            WhereSQL = whereString;
            return this;
        }

        /// <summary>
        /// 设置简单where查询
        /// </summary>
        /// <param name="whereColumns"></param>
        /// <returns></returns>
        public SQLHelper Where(IList<string> whereColumns)
        {
            WhereParamters = whereColumns;
            return this;
        }

        /// <summary>
        /// 设置排序
        /// </summary>
        /// <param name="orderColumn">查询字段</param>
        /// <param name="isAsc">是否正序</param>
        /// <returns></returns>
        public SQLHelper Order(string orderColumn, bool isAsc)
        {
            OrderParamaters.Add(orderColumn, isAsc);
            return this;
        }

        /// <summary>
        /// 设置分组
        /// </summary>
        /// <param name="groupColumn"> 分组字段</param>
        /// <returns></returns>
        public SQLHelper Group(string groupColumn)
        {
            GroupByParamaters.Add(groupColumn);
            return this;
        }


        /// <summary>
        /// 构造新增SQL
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public SQLHelper GreateInsert(IList<string> columns)
        {
            SQLType = SQLType.INSERT;
            EditParamaters = columns;
            return this;
        }

        /// <summary>
        /// 构造修改SQL
        /// </summary>
        /// <param name="updateColumns"></param>
        /// <param name="whereColumns"></param>
        /// <returns></returns>
        public SQLHelper GreateUpdate(IList<string> updateColumns, IList<string> whereColumns)
        {
            SQLType = SQLType.UPDATE;
            EditParamaters = updateColumns;
            WhereParamters = whereColumns;
            return this;
        }


        /// <summary>
        /// 构造修改SQL
        /// </summary>
        /// <param name="updateColumns"></param>
        /// <param name="whereString"></param>
        /// <returns></returns>
        public SQLHelper GreateUpdate(IList<string> updateColumns, string whereString)
        {
            SQLType = SQLType.UPDATE;
            EditParamaters = updateColumns;
            WhereSQL = whereString;
            return this;
        }

        /// <summary>
        /// 构造新增SQL
        /// </summary>
        /// <param name="whereColumns"></param>
        /// <returns></returns>
        public SQLHelper GreateDelete(IList<string> whereColumns)
        {
            SQLType = SQLType.DELETE;
            WhereParamters = whereColumns;
            return this;
        }

        /// <summary>
        /// 构造新增SQL
        /// </summary>
        /// <param name="whereString"></param>
        /// <returns></returns>
        public SQLHelper GreateDelete(string whereString)
        {
            SQLType = SQLType.DELETE;
            WhereSQL = whereString;
            return this;
        }


        /// <summary>
        /// 分页查询语句生产
        /// </summary>
        /// <returns></returns>
        private string GetPageSelectSQL()
        {
            string strTop = string.Empty;
            string strOrder = string.Empty;
            int pageint = PageHelper.rows * (PageHelper.page - 1);

            //SELECT Columns
            string columns = GetColumns();

            if (PageHelper.rows >= 0) strTop += " TOP " + PageHelper.rows + " ";

            string sort = GetOrder();
            if (!string.IsNullOrEmpty(sort)) strOrder += " ORDER BY " + sort;

            string strSql = string.Format(SELECT_PAGE_SQL, strTop, strOrder, columns, TableName, GetWhere(), pageint);

            return strSql;
        }

        /// <summary>
        /// 查询语句生成
        /// </summary>
        /// <returns></returns>
        private string GetSelectSql()
        {
            if (string.IsNullOrEmpty(TableName))
                return string.Empty;

            StringBuilder beforeFrom = new StringBuilder();
            StringBuilder afterWhere = new StringBuilder();

            //TOP
            if (TopCount > 0)
            {
                beforeFrom.Append(string.Format(" TOP {0}", TopCount));
            }

            //DISTINCT 
            if (IsDistinct)
            {
                beforeFrom.Append(" DISTINCT ");
            }

            //SELECT Columns
            string columns = GetColumns();
            beforeFrom.Append(string.Format(" {0}", columns));

            //WHERE
            afterWhere.Append(string.Format("{0}", GetWhere()));

            //GROUP BY
            if (GroupByParamaters.Count > 0)
            {
                StringBuilder group = new StringBuilder();
                foreach (var param in GroupByParamaters)
                {
                    string columnName = param;
                    int asIndex = columnName.ToLower().IndexOf(" as ", StringComparison.Ordinal);
                    if (asIndex >= 0)
                    {
                        columnName = columnName.ToLower().Substring(0, asIndex);
                    }
                    group.Append(string.Format(" {0} ", columnName));
                }
                afterWhere.Append(string.Format(" GROUP BY {0} ", group));
            }

            //ORDER BY
            if (OrderParamaters.Count > 0)
            {
                afterWhere.Append(string.Format(" ORDER BY {0} ", GetOrder()));
            }

            return string.Format(SELECT_SQL, TableName, beforeFrom.ToString(), afterWhere.ToString());
        }

        /// <summary>
        /// 新增语句生成
        /// </summary>
        /// <returns></returns>
        private string GetInsertSql()
        {
            if (string.IsNullOrEmpty(TableName) || EditParamaters.Count == 0)
                return string.Empty;

            StringBuilder insertColumn = new StringBuilder();
            StringBuilder insertValue = new StringBuilder();
            foreach (var p in EditParamaters)
            {
                insertColumn.Append(string.Format("{0},", p));
                insertValue.Append(string.Format("@{0},", p));
            }

            return string.Format(INSERT_SQL, TableName, insertColumn.ToString().TrimEnd(','), insertValue.ToString().TrimEnd(','));
        }

        /// <summary>
        /// 修改语句生成
        /// </summary>
        /// <returns></returns>
        private string GetUpdateSql()
        {
            if (string.IsNullOrEmpty(TableName) || EditParamaters.Count == 0)
                return string.Empty;

            StringBuilder updateParam = new StringBuilder();
            foreach (var p in EditParamaters)
            {
                updateParam.Append(string.Format("{0}=@{0},", p));
            }

            return string.Format(UPDATE_SQL, TableName, updateParam.ToString().TrimEnd(','), GetWhere());
        }

        /// <summary>
        /// 删除语句生成
        /// </summary>
        /// <returns></returns>
        private string GetDeleteSql()
        {
            if (string.IsNullOrEmpty(TableName))
                return string.Empty;

            return string.Format(DELETE_SQL, TableName, GetWhere());
        }

        /// <summary>
        /// 获得where语句
        /// </summary>
        /// <returns></returns>
        private string GetWhere()
        {
            if (!string.IsNullOrEmpty(WhereSQL))
                return WhereSQL;

            StringBuilder where = new StringBuilder(" 1=1 ");
            for (int i = 0; i < WhereParamters.Count; i++)
            {
                where.Append(string.Format(" AND {0}=@{0} ", WhereParamters[i]));

            }
            return where.ToString();
        }

        /// <summary>
        /// 获得排序
        /// </summary>
        /// <returns></returns>
        private string GetOrder()
        {
            StringBuilder order = new StringBuilder();
            foreach (var param in OrderParamaters)
            {
                string columnName = param.Key;
                int asIndex = columnName.ToLower().IndexOf(" as ", StringComparison.Ordinal);
                if (asIndex >= 0)
                {
                    columnName = columnName.ToLower().Substring(0, asIndex);
                }

                order.Append(string.Format(" {0} ", columnName));
                order.Append(param.Value ? "ASC," : "DESC,");
            }

            return order.ToString().TrimEnd(',');
        }

        /// <summary>
        /// 获得要查询的字段
        /// </summary>
        /// <returns></returns>
        private string GetColumns()
        {
            StringBuilder ColumnParam = new StringBuilder();

            if (SelectColumns.Count == 0)
            {
                ColumnParam.Append(" * ");
            }
            else
            {
                foreach (var col in SelectColumns)
                {
                    ColumnParam.Append(string.Format("{0},", col));
                }
            }

            return ColumnParam.ToString().TrimEnd(',');
        }

        /// <summary>
        /// 重载tostring方法
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            switch (SQLType)
            {
                case SQLType.PAGE_SELECT:
                    return GetPageSelectSQL();
                case SQLType.SELECT:
                    return GetSelectSql();
                case SQLType.INSERT:
                    return GetInsertSql();
                case SQLType.UPDATE:
                    return GetUpdateSql();
                case SQLType.DELETE:
                    return GetDeleteSql();
            }
            return string.Empty;
        }
    }
    #endregion
}
